﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Web;
using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Model;
using NPOI.HPSF;
using NPOI.SS.UserModel;
using NPOI.SS.Util;

namespace Facade.Excel
{
    /// <summary>
    /// 格式化样式
    /// </summary>
    public enum FormatStyle
    {
        /// <summary>
        /// 百分比
        /// </summary>
        Percent,
        /// <summary>
        /// 保留两位小数
        /// </summary>
        ToFix2,
        /// <summary>
        /// 日期YYYY-MM-dd
        /// </summary>
        Date,
        /// <summary>
        /// 日期、时间 YYYY-MM-dd hh:mm:ss
        /// </summary>
        DateTime
    }

    public class ExportFacade
    {
        #region 单个Sheet导出

        /// <summary>
        /// 单Sheet Web导出
        /// </summary>
        /// <param name="dtSource">DataTable数据源</param>
        /// <param name="strHeaderText">表头文本</param>
        /// <param name="strFileName">文件名</param>
        /// <param name="formatOptions">格式化选项</param>
        /// <param name="currentModuleName">模块名称 例如：企业毛利率分析</param>
        /// <param name="footerAvgColumns">平均汇总的列</param>
        /// <param name="footerSumColumns">总和汇总的列</param>
        /// <param name="mergeColumnIndexs">需要合并的列</param>
        public void ExportByWeb(DataTable dtSource, string strHeaderText, string strFileName, Dictionary<int, FormatStyle> formatOptions, string currentModuleName, List<int> footerAvgColumns, List<int> footerSumColumns, List<int> mergeColumnIndexs)
        {
            HSSFWorkbook workBook = InitializeWorkbook(null, null, null, currentModuleName);
            Sheet sheet = workBook.CreateSheet();
            CreateRowHeader(workBook, sheet, strHeaderText, dtSource);
            FillRowData(workBook, sheet, 2, dtSource, formatOptions, footerAvgColumns, footerSumColumns, mergeColumnIndexs);

            using (MemoryStream ms = RenderExcelToDownload(workBook))
            {
                byte[] data = ms.ToArray();
                ExportByWeb(data, strFileName);
            }
        }

        /// <summary>
        /// 用于Web导出【单Sheet】
        /// </summary>
        /// <param name="dtSource">DataTable数据源</param>
        /// <param name="strHeaderText">表头文本</param>
        /// <param name="strFileName">文件名</param>
        /// <param name="formatOptions">格式化选项</param>
        /// <param name="currentModuleName">模块名称 例如：企业毛利率分析</param>
        public void ExportByWeb(DataTable dtSource, string strHeaderText, string strFileName, Dictionary<int, FormatStyle> formatOptions, string currentModuleName, List<int> footerAvgColumns, List<int> footerSumColumns)
        {
            ExportByWeb(dtSource, strHeaderText, strFileName, formatOptions, currentModuleName, footerAvgColumns, footerSumColumns, null);
        }

        /// <summary>
        /// 用于Web导出【单Sheet】
        /// </summary>
        /// <param name="dtSource">DataTable数据源</param>
        /// <param name="strHeaderText">表头文本</param>
        /// <param name="strFileName">文件名</param>
        /// <param name="formatOptions">格式化选项</param>
        /// <param name="currentModuleName">模块名称 例如：企业毛利率分析</param>
        public void ExportByWeb(DataTable dtSource, string strHeaderText, string strFileName, Dictionary<int, FormatStyle> formatOptions, string currentModuleName)
        {
            ExportByWeb(dtSource, strHeaderText, strFileName, formatOptions, currentModuleName, null, null, null);
        }

        #endregion

        #region 多个Sheet导出

        /// <summary>
        /// DataTable导出到Excel的MemoryStream【多Sheet】
        /// </summary>
        /// <param name="filterExpression">搜索表达式 例如：businessNature='{0}'</param>
        /// <param name="sheetList">Sheet列表数据</param>
        /// <param name="columns">源列名集合及中文列名集合</param>
        /// <param name="source">源DataTable</param>
        /// <param name="strHeaderText">表头文本</param>
        /// <param name="formatOptions">格式化列表</param>
        /// <param name="currentModuleName">模块名称 例如：企业毛利率分析</param>
        /// <returns></returns>
        public MemoryStream Export(string filterExpression,Dictionary<string,string> sheetList,Dictionary<string,string> columns,DataTable source, string strHeaderText, Dictionary<int, FormatStyle> formatOptions, string currentModuleName)
        {
            HSSFWorkbook workbook = InitializeWorkbook(null, null, null, currentModuleName);

            string[] sourceColumns = columns.Keys.ToArray<string>();
            string[] cheColumns = columns.Values.ToArray<string>();

            foreach (KeyValuePair<string,string> sheetItem in sheetList)
            {
                Sheet sheet = workbook.CreateSheet(sheetItem.Value);

                #region 数据转换处理
                DataTable dtSource = source.Clone();

                string newFilterExpression = string.Format(filterExpression, sheetItem.Key);
                DataRow[] rows = source.Select(newFilterExpression);

                for (int k = 0; k < rows.Length; k++)
                {
                    dtSource.ImportRow(rows[k]);
                }

                dtSource = dtSource.DefaultView.ToTable(false, sourceColumns);
                for (int i = 0; i < cheColumns.Length; i++)
                {
                    dtSource.Columns[i].ColumnName = cheColumns[i];
                }

                #endregion

                CreateRowHeader(workbook, sheet, strHeaderText, dtSource);
                FillRowData(workbook, sheet, 2, dtSource, formatOptions, null, null, null);
            }

            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;

                return ms;
            }
        }

        /// <summary>
        /// 用于Web导出【多Sheet】
        /// </summary>
        /// <param name="filterExpression">搜索表达式 例如：businessNature='{0}'</param>
        /// <param name="sheetList">Sheet列表数据</param>
        /// <param name="columns">源列名集合及中文列名集合</param>
        /// <param name="source">源DataTable</param>
        /// <param name="strHeaderText">表头文本</param>
        /// <param name="formatOptions">格式化列表</param>
        /// <param name="currentModuleName">模块名称 例如：企业毛利率分析</param>
        public void ExportByWeb(string filterExpression, Dictionary<string, string> sheetList, Dictionary<string, string> columns, DataTable source, string strHeaderText, string strFileName, Dictionary<int, FormatStyle> formatOptions, string currentModuleName)
        {
            byte[] data = Export(filterExpression, sheetList, columns, source, strHeaderText, formatOptions, currentModuleName).GetBuffer();
            ExportByWeb(data, strFileName);
        }

        #endregion

        #region 自定义工作簿

        /// <summary>
        /// 将字节流发送到响应输出中，提供下载
        /// </summary>
        /// <param name="date"></param>
        /// <param name="fileName"></param>
        public void ExportByWeb(byte[] data,string fileName)
        {
            fileName = HttpUtility.UrlEncode(fileName, Encoding.UTF8);
            fileName = fileName.Replace("+", "%20");
            HttpContext curContext = HttpContext.Current;

            // 设置编码和附件格式
            curContext.Response.ContentType = "application/vnd.ms-excel";
            curContext.Response.ContentEncoding = Encoding.UTF8;
            curContext.Response.Charset = "UTF-8";
            curContext.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName);
            curContext.Response.BinaryWrite(data);
            curContext.Response.End();
        }

        /// <summary>
        /// 用于Web导出
        /// </summary>
        /// <param name="workbook">工作簿对象</param>
        /// <param name="strHeaderText">表头文本</param>
        /// <param name="strFileName">文件名</param>
        public void ExportByWeb(HSSFWorkbook workbook, string strHeaderText,string strFileName)
        {
            using (MemoryStream excelStream = RenderExcelToDownload(workbook))
            {
                byte[] data = excelStream.GetBuffer();
                ExportByWeb(data, strFileName);
            }
        }

        /// <summary>
        /// 新建一个工作簿
        /// </summary>
        /// <param name="companyName">公司名称</param>
        /// <param name="author">作者</param>
        /// <param name="appName">应用程序名称</param>
        /// <param name="currentModuleName">当前模块名称</param>
        /// <returns>工作簿对象</returns>
        public HSSFWorkbook InitializeWorkbook(string companyName,string author,string appName,string currentModuleName)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = companyName ?? "杭州农副产品物流网络科技有限公司";
            workbook.DocumentSummaryInformation = dsi;

            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.Author = author ?? "杭州农副产品物流网络科技有限公司";
            si.ApplicationName = appName ?? "采购配送系统";
            si.LastAuthor = author ?? "tianzh";
            si.Comments = currentModuleName;
            si.Title = currentModuleName;
            si.Subject = currentModuleName;
            si.CreateDateTime = DateTime.Now;
            workbook.SummaryInformation = si;

            return workbook;
        }

        /// <summary>
        /// 获取DataTable数据表列的长度
        /// </summary>
        /// <param name="dtSource">DataTable数据表</param>
        /// <returns>包含列长度的数组</returns>
        private int[] GetColumnWidth(DataTable dtSource)
        {
            int[] arrColWidth = new int[dtSource.Columns.Count];

            foreach (DataColumn item in dtSource.Columns)
            {
                arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName).Length;
            }
            for (int i = 0; i < dtSource.Rows.Count; i++)
            {
                for (int j = 0; j < dtSource.Columns.Count; j++)
                {
                    int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
                    if (intTemp > arrColWidth[j])
                    {
                        arrColWidth[j] = intTemp;
                    }
                }
            }

            return arrColWidth;
        }

        /// <summary>
        /// 创建Excel标题行
        /// </summary>
        /// <param name="workbook">工作簿对象</param>
        /// <param name="sheet">Excel单页</param>
        /// <param name="strHeaderText">标题行文本</param>
        /// <param name="dtSource">DataTable数据源</param>
        public void CreateRowHeader(HSSFWorkbook workbook, Sheet sheet, string strHeaderText, DataTable dtSource)
        {
            CreateRowHeader(workbook, sheet, 0, strHeaderText, dtSource);
        }

        /// <summary>
        /// 创建Excel标题行
        /// </summary>
        /// <param name="workbook">工作簿对象</param>
        /// <param name="sheet">Excel单页</param>
        /// <param name="strHeaderText">标题行文本</param>
        /// <param name="dtSource">DataTable数据源</param>
        public void CreateRowHeader(HSSFWorkbook workbook, Sheet sheet, int startRowIndex, string strHeaderText, DataTable dtSource)
        {
            #region 表头及样式
            {
                Row headerRow = sheet.CreateRow(startRowIndex);
                headerRow.HeightInPoints = 25;
                headerRow.CreateCell(0).SetCellValue(strHeaderText);

                CellStyle headStyle = workbook.CreateCellStyle();
                headStyle.Alignment = HorizontalAlignment.CENTER;
                Font font = workbook.CreateFont();
                font.FontHeightInPoints = 20;
                font.Boldweight = 700;
                headStyle.SetFont(font);
                headStyle.WrapText = true;

                headerRow.GetCell(0).CellStyle = headStyle;
                sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
            }
            #endregion

            #region 列头及样式
            {
                int[] arrColWidth = GetColumnWidth(dtSource);

                Row headerRow = sheet.CreateRow(startRowIndex+1);

                CellStyle headStyle = workbook.CreateCellStyle();
                headStyle.Alignment = HorizontalAlignment.CENTER;
                Font font = workbook.CreateFont();
                font.FontHeightInPoints = 10;
                font.Boldweight = 700;
                headStyle.SetFont(font);

                foreach (DataColumn column in dtSource.Columns)
                {
                    headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                    headerRow.GetCell(column.Ordinal).CellStyle = headStyle;

                    //设置列宽
                    sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);

                }
            }
            #endregion
        }

        /// <summary>
        /// 填充Excel数据行
        /// </summary>
        /// <param name="workbook">工作簿对象</param>
        /// <param name="sheet">Excel单页</param>
        /// <param name="startRowIndex">数据行开始索引，默认为2</param>
        /// <param name="dtSource">DataTable数据源</param>
        /// <param name="formatOptions">格式化选项</param>
        /// <param name="footerAvgColumns">平均汇总的列</param>
        /// <param name="footerSumColumns">总和汇总的列</param>
        /// <param name="mergeColumnIndexs">合并的列</param>
        public void FillRowData(HSSFWorkbook workbook, Sheet sheet, int startRowIndex, DataTable dtSource, 
            Dictionary<int, FormatStyle> formatOptions,
            List<int> footerAvgColumns, List<int> footerSumColumns,
            List<int> mergeColumnIndexs)
        {
            #region 格式化选项

            CellStyle dateTimeStyle = workbook.CreateCellStyle();
            DataFormat dateTimeFormat = workbook.CreateDataFormat();
            dateTimeStyle.DataFormat = dateTimeFormat.GetFormat("yyyy-mm-dd hh:mm:ss");

            CellStyle dateStyle = workbook.CreateCellStyle();
            DataFormat format = workbook.CreateDataFormat();
            dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");

            CellStyle percentStyle = workbook.CreateCellStyle();
            percentStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");

            CellStyle toFix2CellStyle = workbook.CreateCellStyle();
            toFix2CellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");

            #endregion

            for (int i = 0; i < dtSource.Rows.Count; i++)
            {
                Row dataRow = sheet.CreateRow(startRowIndex);

                #region 填充内容

                for (int j = 0; j < dtSource.Columns.Count; j++)
                {
                    Cell newCell = dataRow.CreateCell(j);

                    string drValue = dtSource.Rows[i][j].ToString();

                    switch (dtSource.Columns[j].DataType.ToString())
                    {
                        case "System.String"://字符串类型
                            newCell.SetCellValue(drValue);
                            break;
                        case "System.DateTime"://日期类型
                            DateTime dateV;
                            DateTime.TryParse(drValue, out dateV);
                            if (formatOptions != null && formatOptions.Count > 0)
                            {
                                // 格式化设置
                                if (formatOptions.ContainsKey(j))
                                {
                                    switch (formatOptions[j])
                                    {
                                        case FormatStyle.Date: newCell.CellStyle = dateStyle;
                                            break;
                                        default: newCell.CellStyle = dateTimeStyle;
                                            break;
                                    }
                                }
                            }
                            else
                            {
                                newCell.CellStyle = dateTimeStyle;
                            }

                            newCell.SetCellValue(dateV);

                            break;
                        case "System.Boolean"://布尔型
                            bool boolV = false;
                            bool.TryParse(drValue, out boolV);
                            newCell.SetCellValue(boolV);
                            break;
                        case "System.Int16"://整型
                        case "System.Int32":
                        case "System.Int64":
                        case "System.Byte":
                            int intV = 0;
                            int.TryParse(drValue, out intV);
                            newCell.SetCellValue(intV);
                            break;
                        case "System.Decimal"://浮点型
                        case "System.Double":
                            double doubV = 0;
                            double.TryParse(drValue, out doubV);

                            if (formatOptions != null && formatOptions.Count > 0)
                            {
                                // 格式化设置
                                if (formatOptions.ContainsKey(j))
                                {
                                    switch (formatOptions[j])
                                    {
                                        case FormatStyle.Percent: newCell.CellStyle = percentStyle;
                                            break;
                                        case FormatStyle.ToFix2: newCell.CellStyle = toFix2CellStyle;
                                            break;
                                    }
                                }
                            }

                            newCell.SetCellValue(doubV);
                            break;
                        case "System.DBNull"://空值处理
                            newCell.SetCellValue("");
                            break;
                        default:
                            newCell.SetCellValue("");
                            break;
                    }

                }
                #endregion

                startRowIndex++; 
            }

            #region 合并单元格

            if (mergeColumnIndexs != null)
            {
                foreach (int startIndex in mergeColumnIndexs)
                {
                    int t1, t2, initSpan;
                    string temp;
                    for (t1 = 2; t1 <= sheet.LastRowNum; t1++)
                    {
                        initSpan = 1;

                        if (sheet.GetRow(t1).GetCell(startIndex) != null)
                        {

                            Cell cell = sheet.GetRow(t1).GetCell(startIndex);
                            switch (cell.CellType)
                            {
                                case CellType.NUMERIC: temp = sheet.GetRow(t1).GetCell(startIndex).DateCellValue.ToString();
                                    break;
                                case CellType.STRING: temp = sheet.GetRow(t1).GetCell(startIndex).StringCellValue;
                                    break;
                                default: temp = sheet.GetRow(t1).GetCell(startIndex).StringCellValue;
                                    break;
                            }

                            for (t2 = t1 + 1; t2 <= sheet.LastRowNum; t2++)
                            {
                                if (sheet.GetRow(t2).GetCell(startIndex) != null)
                                {
                                    Cell cellCol = sheet.GetRow(t2).GetCell(startIndex);
                                    string current = string.Empty;
                                    switch (cellCol.CellType)
                                    {
                                        case CellType.NUMERIC: current = sheet.GetRow(t2).GetCell(startIndex).DateCellValue.ToString();
                                            break;
                                        case CellType.STRING: current = sheet.GetRow(t2).GetCell(startIndex).StringCellValue;
                                            break;
                                        default: current = sheet.GetRow(t2).GetCell(startIndex).StringCellValue;
                                            break;
                                    }
                                    if (String.Compare(temp, current) == 0)
                                    {
                                        initSpan++;
                                        CellRangeAddress region = new CellRangeAddress(t1, t2, startIndex, startIndex);
                                        sheet.AddMergedRegion(region);
                                        CellStyle style = workbook.CreateCellStyle();
                                        style.VerticalAlignment = VerticalAlignment.BOTTOM;
                                        sheet.GetRow(t1).GetCell(startIndex).CellStyle = style;
                                    }
                                    else
                                    {
                                        break;
                                    }
                                }
                            }
                            t1 = t2 - 1;
                        }
                    }
                }
            }

            #endregion

            #region 汇总运算

            string[] alphabet = { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z" };

            CellStyle footerRowStyle = workbook.CreateCellStyle();

            #region 平均值汇总运算

            if (footerAvgColumns != null && footerAvgColumns.Count > 0)
            {
                startRowIndex++;

                Row footerAvgTitleRow = sheet.CreateRow(startRowIndex++);
                footerAvgTitleRow.RowStyle = footerRowStyle;

                Cell footerAvgTitleCell = footerAvgTitleRow.CreateCell(0);
                footerAvgTitleCell.SetCellValue("平均：");

                Row footerAvgRow = sheet.CreateRow(startRowIndex++);
                for (int i = 0; i < footerAvgColumns.Count; i++)
                {
                    Cell footerAvg5Cell = footerAvgRow.CreateCell(footerAvgColumns[i]);

                    switch (formatOptions[footerAvgColumns[i]])
                    {
                        case FormatStyle.Percent: footerAvg5Cell.CellStyle = percentStyle;
                            break;
                        case FormatStyle.ToFix2: footerAvg5Cell.CellStyle = toFix2CellStyle;
                            break;
                    }

                    string num = alphabet[footerAvgColumns[i]];
                    string formulaExpr = string.Format("AVERAGE(${0}3:${0}{1})", num, dtSource.Rows.Count + 3);
                    footerAvg5Cell.CellFormula = formulaExpr;
                }
            } 

            #endregion

            #region 总和汇总运算

            if (footerSumColumns != null && footerSumColumns.Count > 0)
            {
                startRowIndex++;
                Row footerSumTitleRow = sheet.CreateRow(startRowIndex++);
                footerSumTitleRow.RowStyle = footerRowStyle;

                Cell footerSumTitleCell = footerSumTitleRow.CreateCell(0);
                footerSumTitleCell.SetCellValue("总计：");

                Row footerSumRow = sheet.CreateRow(startRowIndex++);
                for (int i = 0; i < footerSumColumns.Count; i++)
                {
                    Cell footerSumCell = footerSumRow.CreateCell(footerSumColumns[i]);

                    switch (formatOptions[footerSumColumns[i]])
                    {
                        case FormatStyle.Percent: footerSumCell.CellStyle = percentStyle;
                            break;
                        case FormatStyle.ToFix2: footerSumCell.CellStyle = toFix2CellStyle;
                            break;
                    }

                    string num = alphabet[footerSumColumns[i]];
                    string formulaExpr = string.Format("SUM(${0}3:${0}{1})", num, dtSource.Rows.Count + 3 + 1);
                    footerSumCell.CellFormula = formulaExpr;
                }
            } 

            #endregion

            #endregion
        }

        /// <summary>
        /// 渲染Excel内存流
        /// </summary>
        /// <param name="workbook">工作簿对象</param>
        /// <returns></returns>
        public MemoryStream RenderExcelToDownload(HSSFWorkbook workbook)
        {
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;

                return ms;
            }
        }

        /// <summary>
        /// 获取系统内置的格式
        /// </summary>
        /// <param name="format">格式字符串 例如：0.00</param>
        /// <returns></returns>
        public short GetBuiltinFormat(string format)
        {
            return HSSFDataFormat.GetBuiltinFormat(format);
        }

        /// <summary>
        /// 设置自定义的格式
        /// </summary>
        /// <param name="workBook">工作簿对象</param>
        /// <param name="format">格式化字符串 例如：yyyy-MM-dd</param>
        /// <returns></returns>
        public short GetDataFormat(HSSFWorkbook workBook,string format)
        {
            DataFormat dataFormat = workBook.CreateDataFormat();
            return dataFormat.GetFormat(format);            
        }

        #endregion
    }
}
